package com.isyscore.os.metadata.database;

import com.isyscore.os.core.sqlcore.PGBuilder;
import com.isyscore.os.metadata.enums.DataSourceTypeEnum;
import com.isyscore.os.metadata.model.dto.DataSourceDTO;
import com.isyscore.os.metadata.model.vo.ResultVO;
import org.apache.commons.lang3.StringUtils;

import java.util.List;
import java.util.stream.Collectors;

/**
 * @author : zhan9yn
 * @version : 1.0
 * @date : 2021/12/2 10:03 上午
 */
public class PGSQLDatabase extends AbstractDatabase{


    {
        dbType = DataSourceTypeEnum.PGSQL;
        //时间类型
        DATE_TYPE.add("DATE");
        DATE_TYPE.add("TIMESTAMP");
        DATE_TYPE.add("TIME");

        //字段类型
        COLUMN_TYPE.put("varchar", "varchar(255)");
        COLUMN_TYPE.put("int2", "int2");
        COLUMN_TYPE.put("int4", "int4");
        COLUMN_TYPE.put("int8", "int8");
        COLUMN_TYPE.put("float4", "float8");
        COLUMN_TYPE.put("DOUBLE", "DOUBLE");
        COLUMN_TYPE.put("char", "char(255)");

        COLUMN_TYPE.put("text", "text");
        COLUMN_TYPE.put("date", "date");
        COLUMN_TYPE.put("time", "time(6)");
        COLUMN_TYPE.put("timestamp", "timestamp(6)");

        builder = new PGBuilder();
    }

    private final String QUOTE = "\"";

    @Override
    public String escapeTbName(String originTableName, String databaseName) {
        String schema = escapeDbName(databaseName);
        return this.QUOTE + schema + QUOTE + "." + this.QUOTE + originTableName + QUOTE;
    }

    @Override
    public String escapeColName(String originColName) {
        return this.QUOTE + originColName + QUOTE;
    }

    @Override
    public String withOutEscapeColName(String escapeColName) {
        return escapeColName.replaceAll(QUOTE,"");
    }
    @Override
    public String getParseStr2DateEl(String dateVal) {
        return "to_timestamp(" + dateVal + " ,'YYYY-MM-DD HH24:MI:SS')";
    }

    @Override
    public String getParseDate2StrEl(String dateVal) {
        return null;
    }

    @Override
    public String getParseStr2IntEl(String dateVal) {
        return "cast("+dateVal+" as INTEGER)";
    }

    @Override
    public String jdbcUrl(String ip, int port, String dbName, String basicType, String basicValue) {
        if (StringUtils.isBlank(dbName)) {
            dbName = "";
        } else if (dbName.contains(".")) {
            dbName = dbName.split("\\.")[0];
        }
        return String.format(dbType.getUrl(), ip, port, dbName);
    }

    @Override
    public String tableStructSql(String tableName, String dbName) {
        String SELECT_TABLE_COL = "SELECT a.attnum,\n" +
                "c.relname AS \"tableName\",\n" +
                "a.attname AS \"columnName\",\n" +
                "a.attname AS \"oldName\",\n" +
                "t.typname AS \"dataType\",\n" +
                "t.typname || '(' || a.attlen || ')' AS \"columnType\",\n" +
                "a.atttypmod AS \"lengthvar\",\n" +
                "a.attnotnull AS \"notnull\",\n" +
                "(CASE WHEN ( SELECT COUNT(*) FROM pg_constraint WHERE conrelid = a.attrelid AND conkey[1]= attnum AND contype = 'p' ) > 0 THEN\n" +
                "        'PRI' ELSE '' \n" +
                "        END ) AS \"columnKey\"," +
                "CASE \n" +
                "\tWHEN b.description is null THEN\n" +
                "\t\ta.attname\n" +
                "\tELSE\n" +
                "\t\tb.description\n" +
                "END AS \"columnComment\"\n" +
                "FROM pg_class c,\n" +
                "pg_attribute a\n" +
                "LEFT OUTER JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid,\n" +
                "pg_type t\n" +
                "WHERE c.relname = '%s'\n" +
                "and a.attnum > 0\n" +
                "and a.attrelid = c.oid\n" +
                "and a.atttypid = t.oid\n" +
                "AND c.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = '%s')\n" +
                "ORDER BY a.attnum";
        return String.format(SELECT_TABLE_COL, tableName, dbName);
    }

    public static void main(String[] args) {
        final PGSQLDatabase pgsqlDatabase = new PGSQLDatabase();
        System.out.println(pgsqlDatabase.tableStructSql("tablename", "dbname"));
    }

    @Override
    public String tableListSql(String dbName) {
        final String SELECT_TABLE = "select tablename as \"tableName\" from pg_tables where schemaname = '%s' order by \"tableName\"";
        return String.format(SELECT_TABLE, dbName);
    }

    @Override
    public String linkTableSql(String columns, String dbName) {
        final String LINK_TABLE = "SELECT a.attnum,\n" +
                "c.relname AS \"tableName\",\n" +
                "a.attname AS \"columnName\",\n" +
                "a.attname AS \"oldName\",\n" +
                "t.typname AS \"dataType\",\n" +
                "t.typname || '(' || a.attlen || ')' AS \"columnType\",\n" +
                "a.atttypmod AS \"lengthvar\",\n" +
                "a.attnotnull AS \"notnull\",\n" +
                "CASE \n" +
                "\tWHEN b.description is null THEN\n" +
                "\t\ta.attname\n" +
                "\tELSE\n" +
                "\t\tb.description\n" +
                "END AS \"columnComment\"\n" +
                "FROM pg_class c,\n" +
                "pg_attribute a\n" +
                "LEFT OUTER JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid,\n" +
                "pg_type t\n" +
                "WHERE a.attnum > 0\n" +
                "and a.attrelid = c.oid\n" +
                "and a.atttypid = t.oid\n" +
                "AND c.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = '%s')\n" +
                "AND c.relname IN (select pg_class.relname from pg_attribute left outer join pg_class on  pg_class.oid = pg_attribute.attrelid WHERE pg_attribute.attname IN (%s))\n" +
                "ORDER BY a.attnum";
        return String.format(LINK_TABLE, dbName, columns);
    }

    @Override
    public String renameTableSql(String oldTableName, String newTableName) {
        final String RENAME_TABLE = "alter table %s rename to \"%s\"";
        return String.format(RENAME_TABLE, oldTableName, newTableName);
    }

    @Override
    public String dropTableSql(String tableName) {
        return String.format("DROP TABLE %s", tableName);
    }

    @Override
    public String getQuote() {
        return QUOTE;
    }

    @Override
    public String escapeDbName(String dbName) {
        if (!dbName.contains(".")) {
            //默认返回guest模式
            return dbName;
        }
        return dbName.split("\\.")[1];
    }

    public List<String> schemas(DataSourceDTO dataSourceDTO) {
        String SCHEMAS = "SELECT nspname AS name FROM pg_namespace WHERE nspname NOT LIKE 'pg_%' AND nspname != 'information_schema'";
        ResultVO resultVO = execSqlAndGet(dataSourceDTO, SCHEMAS);
        return resultVO.getContent().parallelStream().map(map -> (String) map.get("name")).collect(Collectors.toList());
    }

    @Override
    public String copyStructSql(String fromTable, String newTable) {
        String sql = "CREATE TABLE %s as SELECT * FROM %s WHERE false";
        return String.format(sql,newTable,fromTable);
    }
}
